![BigQueryでPreviewになったPIVOTとUNPIVOTを試す](https://devio2023-media.developers.io/wp-content/uploads/2020/09/gcp-eyecatch-bigquery_1200x630.png)
BigQueryでPreviewになったPIVOTとUNPIVOTを試す
データアナリティクス事業本部、池田です。
2021/05/10のリリースでBigQueryでPIVOTとUNPIVOTがPreview(執筆時点)となりましたので、
動かしてみました。
【 Release notes 】
以前のブログ ではSQLでPIVOTできなかったので、pandasを使っていました。
(2021/07/20追記)
07/19のリリース で
generally available(GA) になりました。
掲載のSQLはそのままで動くことを確認済みです。
PIVOT
PIVOTを使ってみます。
【 PIVOT operator 】 ※執筆時点ではドキュメントは英語のみ
対象は BigQuery の一般公開データセット
から出生データの natality
サンプルテーブルです。
まずは普通に年・月・性別でGROUP BYして、新生児の平均体重を見てみます。
(今回はPIVOTの確認なので、双子などは考慮せず、 is_male
カラムがTRUEでないものを単純にfemaleとして扱っています。)
SELECT year, month, CASE is_male WHEN TRUE THEN 'male' ELSE 'female' END AS sex, ROUND(AVG(weight_pounds), 2) AS avg_weight_pounds FROM `bigquery-public-data.samples.natality` GROUP BY year, month, sex ORDER BY year, month, sex;
↓
+------+-------+--------+-------------------+ | year | month | sex | avg_weight_pounds | +------+-------+--------+-------------------+ | 1969 | 1 | female | 7.05 | | 1969 | 1 | male | 7.32 | | 1969 | 2 | female | 7.09 | | 1969 | 2 | male | 7.36 | | 1969 | 3 | female | 7.09 | | 1969 | 3 | male | 7.36 | | 1969 | 4 | female | 7.1 | | 1969 | 4 | male | 7.37 | …
同様の内容で性別でPIVOTしてみます。
SELECT year, month, ROUND(female, 2) AS female, ROUND(male, 2) AS male FROM ( SELECT year, month, CASE is_male WHEN TRUE THEN 'male' ELSE 'female' END AS sex, weight_pounds FROM `bigquery-public-data.samples.natality` ) PIVOT ( AVG(weight_pounds) FOR sex IN ('female', 'male') ) ORDER BY year, month;
↓
+------+-------+--------+------+ | year | month | female | male | +------+-------+--------+------+ | 1969 | 1 | 7.05 | 7.32 | | 1969 | 2 | 7.09 | 7.36 | | 1969 | 3 | 7.09 | 7.36 | | 1969 | 4 | 7.1 | 7.37 | | 1969 | 5 | 7.07 | 7.34 | | 1969 | 6 | 7.04 | 7.31 | | 1969 | 7 | 7.05 | 7.31 | | 1969 | 8 | 7.05 | 7.31 | …
ROUND()
(四捨五入)をどこでするのかちょっと迷いましたが、実行できました。
UNPIVOT
UNPIVOTを使ってみます。
【 UNPIVOT operator 】 ※執筆時点ではドキュメントは英語のみ
対象は BigQuery の一般公開データセット
から気象データの gsod
サンプルテーブルです。
gsodでは、各気象現象( fog, rain, snow, hail, thunder, tornado
)が起きたかを、
それぞれのカラムでBooleanとして持っています。
SELECT station_number, year, month, day, fog, rain, snow, hail, thunder, tornado FROM `bigquery-public-data.samples.gsod` ORDER BY station_number, year, month, day;
↓
+----------------+------+-------+-----+-------+-------+-------+-------+---------+---------+ | station_number | year | month | day | fog | rain | snow | hail | thunder | tornado | +----------------+------+-------+-----+-------+-------+-------+-------+---------+---------+ | 8209 | 2009 | 3 | 9 | false | false | false | false | false | false | | 8209 | 2009 | 3 | 10 | false | false | false | false | false | false | | 8209 | 2009 | 3 | 11 | false | false | false | false | false | false | | 8209 | 2009 | 3 | 12 | false | false | false | false | false | false | … | 10010 | 1955 | 2 | 1 | false | false | false | false | false | false | | 10010 | 1955 | 2 | 2 | true | true | true | true | true | true | | 10010 | 1955 | 2 | 3 | false | false | false | false | false | false | | 10010 | 1955 | 2 | 4 | false | false | false | false | false | false | | 10010 | 1955 | 2 | 5 | true | true | true | true | true | true | …
(サンプルデータはなぜか全てtrueか全てfalseのパターンかしか無いようです。)
各気象現象をUNPIVOTしてみます。
SELECT * FROM ( SELECT station_number, year, month, day, fog, rain, snow, hail, thunder, tornado FROM `bigquery-public-data.samples.gsod` ) UNPIVOT ( occurred FOR weather IN (fog, rain, snow, hail, thunder, tornado) ) ORDER BY station_number, year, month, day;
↓
+----------------+------+-------+-----+----------+---------+ | station_number | year | month | day | occurred | weather | +----------------+------+-------+-----+----------+---------+ | 8209 | 2009 | 3 | 9 | false | thunder | | 8209 | 2009 | 3 | 9 | false | snow | | 8209 | 2009 | 3 | 9 | false | tornado | | 8209 | 2009 | 3 | 9 | false | rain | | 8209 | 2009 | 3 | 9 | false | hail | | 8209 | 2009 | 3 | 9 | false | fog | | 8209 | 2009 | 3 | 10 | false | rain | … | 10010 | 1955 | 2 | 1 | false | tornado | | 10010 | 1955 | 2 | 2 | true | hail | | 10010 | 1955 | 2 | 2 | true | rain | | 10010 | 1955 | 2 | 2 | true | fog | | 10010 | 1955 | 2 | 2 | true | snow | | 10010 | 1955 | 2 | 2 | true | thunder | | 10010 | 1955 | 2 | 2 | true | tornado | | 10010 | 1955 | 2 | 3 | false | rain | …
UNPIVOTの時の FOR … IN ()
句のカラム名は「''」でくくらないようです。
PIVOTのカラムを動的に指定する
参考:【 PIVOT in BigQuery 】
PIVOT句
内の IN
に指定する値(PIVOT後に作成されるカラム)は定数でないといけないようで、
例えば↓のような書き方はできないようです。
SELECT * FROM ( SELECT period_start, period_slot_ms, job_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT ) PIVOT ( SUM(period_slot_ms) FOR job_id IN ( SELECT DISTINCT job_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE job_creation_time > '2021-04-09 09:00:00' ) ) ORDER BY period_start;
(対象のテーブルは ジョブのタイムライン情報 です。)
これは、
変数 ( SET
)
と
動的SQL ( EXECUTE IMMEDIATE
)
で回避できるそうです。
DECLARE jobs STRING; SET jobs = ( SELECT CONCAT('("', STRING_AGG(DISTINCT job_id, '", "'), '")') FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE job_creation_time > '2021-04-09 09:00:00' ); EXECUTE IMMEDIATE FORMAT(""" SELECT * FROM ( SELECT period_start, period_slot_ms, job_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE job_id IN %s ) PIVOT ( SUM(period_slot_ms) FOR job_id IN %s ) ORDER BY period_start """, jobs, jobs);
↓
+---------------------+---------------+---------------+---------------+---------------+ | period_start | bqjob_foo1111 | bqjob_foo2222 | bqjob_foo3333 | bqjob_foo4444 | +---------------------+---------------+---------------+---------------+---------------+ | 2021-04-09 09:34:35 | 0 | 6107 | 0 | 7082 | | 2021-04-09 09:34:36 | 0 | 118037 | 0 | 128499 | | 2021-04-09 09:34:37 | 0 | 291121 | 0 | 298169 | | 2021-04-09 09:34:38 | 0 | 542200 | 0 | 537783 | | 2021-04-09 09:34:39 | 0 | 610966 | 0 | 609666 | | 2021-04-09 09:34:40 | 0 | 336490 | 0 | 311824 | | 2021-04-09 09:34:41 | 0 | 79769 | 0 | 77874 | | 2021-04-09 09:34:42 | 0 | 332044 | 0 | 315014 | | 2021-04-09 09:34:43 | 0 | 559797 | 0 | 767923 | | 2021-04-09 09:34:44 | 0 | 318128 | 0 | 184984 | | 2021-04-09 09:34:45 | 0 | 573927 | 0 | 354814 | …
(ちなみに、SQL15行目のFOR…IN句があるので、11行目のWHERE句は要らないような気がしたのですが、
これが無いとテーブル内全ての period_start
に対してピボットテーブルを作るので、
job_id群がNULLだけのレコードが大量にできてしまいました。)
へー。
おわりに
他にもいろいろオプションがあるようなので便利そうなのがあれば試してみようと思います。たぶん。